package com.baturu.vin.honda.service.impl;

import com.baturu.vin.honda.dal.dao.HondaDataTransferDAO;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.healthmarketscience.jackcess.Column;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Table;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.StopWatch;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * 批量mdb导入到MySQL，更新数据使用这个服务
 *
 * @author chenjiahao
 * @time 2019年6月26日 09:48:42
 */
@Service
@Slf4j
public class HondaAccess2MySQLService {

    private final String mdbPassword = "bpcnosmms";

    private final int BATCH_COUNT = 500;

    //只清洗指定表
    private List<String> cleanTable = Lists.newArrayList("ppartt",
            "pbprmt",
            "pbpmtt",
            "hotspots",
            "pblpat",
            "pblmtt",
            "pmotyt",
            "pbldst",
            "pblokt",
            "pgrout",
            "phcolt",
            "ppacot",
            "pmtmot");
    @Autowired
    private HondaDataTransferDAO dataTransferDAO;

    public void mdb2MySQL(String basePath) {
        File dir = new File(basePath);
        List<File> mdbFiles = loopAllFile(dir, ".accdb");
        for (File mdbFile : mdbFiles) {
            Database database = null;
            try {
                database = DatabaseBuilder.open(mdbFile);
                String tablePrefix = StringUtils.substringBetween(mdbFile.getPath(), dir.getAbsolutePath() + File.separator, ".accdb");
                for (Table table : database) {
                    String completeTableName = (tablePrefix + "_" + table.getName() + "_NEW_DATA").toUpperCase();

                    if (dataTransferDAO.queryFinishTable(completeTableName) > 0) {
                        continue;
                    }
                    if (!cleanTable.contains(table.getName())) {
                        continue;
                    }
                    //拼接建表语句
                    StringBuilder createTable = new StringBuilder("CREATE TABLE " + completeTableName + " (\n");
                    Map<String, String> colType = Maps.newHashMap();
                    for (Column column : table.getColumns()) {
                        createTable.append("\t");
                        switch (column.getType().getSQLType()) {
                            case Types.VARCHAR:
                                //这里设置为默认空白字符串是因为access数据库没有null，到了mysql中带null查询就可能查询不到
                                createTable.append(column.getName() + " VARCHAR(" + column.getLength() + ") NOT NULL DEFAULT '' ");
                                colType.put(column.getName(), "VARCHAR");
                                break;
                            case Types.DOUBLE:
                                createTable.append(column.getName() + " DOUBLE DEFAULT NULL ");
                                colType.put(column.getName(), "DOUBLE");
                                break;
                            case Types.LONGVARCHAR:
                                createTable.append(column.getName() + " TEXT ");
                                colType.put(column.getName(), "TEXT");
                                break;
                            case Types.NUMERIC:
                                createTable.append(column.getName() + " NUMERIC(" + column.getPrecision() + "," + column.getScale() + ")");
                                colType.put(column.getName(), "NUMERIC");
                                break;
                            case Types.SMALLINT:
                                createTable.append(column.getName() + " SMALLINT(" + column.getLength() + ")");
                                colType.put(column.getName(), "SMALLINT");
                                break;
                            case Types.INTEGER:
                                createTable.append(column.getName() + " INTEGER(" + column.getLength() + ")");
                                colType.put(column.getName(), "INTEGER");
                                break;
                            default:
                                throw new RuntimeException("发现未料到的类型，请人工确认类型添加到上面去：" + column.getType());
                        }
                        createTable.append(",\n");
                    }
                    createTable.deleteCharAt(createTable.lastIndexOf(",")).append(");\n");
                    dataTransferDAO.ddl(createTable.toString());
                    log.info(String.format("创建数据表 %s", completeTableName));

                    int insertData = insertData(table, colType, completeTableName);
                    dataTransferDAO.finishTable(completeTableName);
                    log.info(String.format("完成迁移数据表[%,d]行 %s \n", insertData, completeTableName));
                }
            } catch (Exception e) {
                //回滚未完成的表
                dataTransferDAO.query("SELECT CONCAT('DROP TABLE ',TABLE_NAME,';') str FROM information_schema.TABLES " +
                        "WHERE TABLE_NAME LIKE '%_new_data' AND TABLE_SCHEMA = 'hyundaikiaepc_v2' AND TABLE_NAME NOT IN (SELECT finish_table.name FROM finish_table WHERE date = DATE(now()))").stream()
                        .map(m -> (String) m.get("str")).forEach(sql -> {
                    dataTransferDAO.ddl(sql);
                    log.info(String.format("删除数据表 %s ", sql));
                });
                throw new RuntimeException(mdbFile.getAbsolutePath(), e);
            } finally {
                if (database != null) {
                    try {
                        database.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    public void backupData() {
        List<String> allTables = dataTransferDAO.query("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'hyundaikiaepc_v2'").stream()
                .map(m -> (String) m.get("TABLE_NAME")).collect(Collectors.toList());

        List<String> newDataTable = dataTransferDAO.query("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%_new_data' AND TABLE_SCHEMA = 'hyundaikiaepc_v2'").stream()
                .map(m -> (String) m.get("TABLE_NAME")).collect(Collectors.toList());

        for (String name : newDataTable) {
            String oldName = StringUtils.replace(name, "_new_data", "");
            if (allTables.contains(oldName)) {
                dataTransferDAO.ddl(String.format("RENAME TABLE %s TO %s_backup_%s", oldName, oldName, new SimpleDateFormat("yyyyMMdd").format(System.currentTimeMillis())));
                log.info(String.format("备份旧表 %s => %s_backup_%s", oldName, oldName, new SimpleDateFormat("yyyyMMdd").format(System.currentTimeMillis())));
            }
            dataTransferDAO.ddl(String.format("RENAME TABLE %s_new_data to %s", oldName, oldName));
            log.info(String.format("替换新表 %s_new_data => %s", oldName, oldName));
        }
    }

    private int insertData(Table table, Map<String, String> colType, String completeTableName) {
        int result = 0;
        List<Map<String, Object>> rows = Lists.newLinkedList();
        for (int i = 0; i < table.getRowCount(); i++) {
            try {
                Map<String, Object> nextRow = table.getNextRow();
                rows.add(nextRow);
                if (rows.size() % BATCH_COUNT == 0 ) {
                    result += batchInsert(colType, completeTableName, rows);
                    rows.clear();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (CollectionUtils.isNotEmpty(rows)) {
            result += batchInsert(colType, completeTableName, rows);
            rows.clear();
        }
        return result;
    }

    private int batchInsert(Map<String, String> colType, String completeTableName, List<Map<String, Object>> rows) {
        if (CollectionUtils.isEmpty(rows)) {
            return 0;
        }
        Map<String, Object> map = rows.get(0);
        List<String> colList = map.keySet().stream().sorted().collect(Collectors.toList());

        List<List<Object>> args = rows.stream().map(m -> colList.stream().map(k -> Optional.ofNullable(m.get(k)).orElse(getDefaultValue(k, colType)))
                .collect(Collectors.toList())).collect(Collectors.toList());

        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        int insert = dataTransferDAO.batchInsert(completeTableName, colList, args);
        stopWatch.stop();
        log.info(String.format("[%dms]插入[%,d]行记录到 %s", stopWatch.getTime(), insert, completeTableName));
        return insert;
    }

    private Object getDefaultValue(String k, Map<String, String> colType) {
        switch (colType.get(k)) {
            case "VARCHAR":
                return "";
            case "DOUBLE":
                return null;
            case "TEXT":
                return "";
            case "NUMERIC":
                return null;
            case "SMALLINT":
                return null;
            case "INTEGER":
                return null;
            default:
                return "";
        }
    }

    private List<File> loopAllFile(File dir, String fileSuffix) {
        List<File> result = Lists.newArrayList();
        if (dir.isDirectory()) {
            for (File file : Optional.ofNullable(dir.listFiles()).orElse(new File[0])) {
                if (file.isFile() && file.getName().endsWith(fileSuffix)) {
                    result.add(file);
                }
                if (file.isDirectory()) {
                    result.addAll(loopAllFile(file, fileSuffix));
                }
            }
        }
        return result;
    }

}
